//
//  DBHandler.m
//  ui_day20_DataBase
//
//  Created by ChenNing on 15/9/11.
//  Copyright (c) 2015年 陈宁. All rights reserved.
//

#import "DBHandler.h"


@implementation DBHandler

+(DBHandler *)shareInstance
{
    // 单例方法的实现
    //1,每一次运行第一次调用这个方法,会创建对象
    //2,不能对单例对象进行release
    static DBHandler *dbHandler = nil;
    if (dbHandler == nil) {
        dbHandler = [[DBHandler alloc] init];
    }
    // 如果指针不为空,直接返回
    return dbHandler;
}

// 打开数据库
- (BOOL)openDB
{
    // 获取document路径
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 创建一个DB文件夹
    NSString *sqlPath = [docPath stringByAppendingPathComponent:@"DB"];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    if (![fileManager fileExistsAtPath:sqlPath]) {
        [fileManager createDirectoryAtPath:sqlPath withIntermediateDirectories:YES attributes:nil error:nil];
    }
    // 拼接数据库文件路径
    NSString *sqlFilePath = [sqlPath stringByAppendingPathComponent:@"mountEverest.db"];
    NSLog(@"%@",sqlFilePath);
    
    
    // 参数1:数据库文件的路径
    // 参数2:数据库指针的地址:  按照路径,如果路径下没有文件,就给dbPoint赋值;如果没有文件就创建一个文件给dbPoint赋值
    int result = sqlite3_open([sqlFilePath UTF8String], &_dbPoint);
    return [self udgeResult:result operation:@"打开"];
}

// 关闭数据库
- (BOOL)closeDB
{
   // 参数:
   int result = sqlite3_close(_dbPoint);
   return [ self udgeResult:result operation:@"关闭"];
}

// 封装一个结果判断的方法
-(BOOL)udgeResult:(int)result operation:(NSString *)name
{
    if (result == 0) {
        NSLog(@"%@成功",name);
        return YES;
    }
    else
    {
        NSLog(@"%@失败,错误代码%d",name,result);
        return NO;
    }
}

// 创建表
- (BOOL)createTable1
{
    NSString *createTableSQL = [NSString stringWithFormat:@"create table CollectModel (index1 integer,requstUrl text,albumTitle text primary key,AlbumCoverSmall text, nickname text, intro text, AlbumPlayTimes text, playUrl64 text, downloadUrl text,  title text, duration text,  coverSmall text, coverLarge text, playtimes text, downloadSize text, createdAt text, rePlayTime text )"];
    int result = sqlite3_exec(_dbPoint, [createTableSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"创建表"];
}

// 创建表
- (BOOL)createTable2
{
    NSString *createTableSQL = [NSString stringWithFormat:@"create table CollectedModel (address text,titelStr text primary key,image text)"];
    int result = sqlite3_exec(_dbPoint, [createTableSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"创建表"];
}

- (BOOL) createTableSearch
{
    NSString *createTableSQL = [NSString stringWithFormat:@"create table SearchModel (titelStr text primary key)"];
    int result = sqlite3_exec(_dbPoint, [createTableSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"创建表"];
}

- (BOOL) insertVideo:(CollectModel *)collect
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into CollectModel values ('%ld', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')",collect.index, collect.requstUrl, collect.albumTitle, collect.AlbumCoverSmall, collect.nickname, collect.intro, collect.AlbumPlayTimes, collect.playUrl64, collect.downloadUrl, collect.title, collect.duration, collect.coverSmall, collect.coverLarge, collect.playtimes, collect.downloadSize, collect.createdAt, collect.rePlayTime];
    int result = sqlite3_exec(_dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"插入"];
}

- (BOOL) insertCollect:(CollectedModel *)collect
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into CollectedModel values ('%@', '%@', '%@')",collect.address, collect.titelStr, collect.image];
    int result = sqlite3_exec(_dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"插入"];
}

- (BOOL) insertSearch:(SearchHistoryModel *)search
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into SearchModel values ('%@')", search.titelStr];
    int result = sqlite3_exec(_dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"插入"];
}

// 更新对象
- (BOOL) updateCollect:(CollectModel *)collect whereTitle:(NSString *)title
{
    NSString *updateSQL = [NSString stringWithFormat:@"update CollectModel set index1='%ld',requstUrl='%@',albumTitle='%@',AlbumCoverSmall='%@',nickname='%@', intro='%@', playUrl64='%@', downloadUrl='%@',title='%@', duration='%@', coverSmall='%@', coverLarge='%@', downloadSize='%@', createdAt='%@', duration='%@',rePlayTime='%@' where albumTitle='%@'",collect.index, collect.requstUrl, collect.albumTitle, collect.AlbumCoverSmall, collect.nickname, collect.intro, collect.AlbumPlayTimes, collect.playUrl64, collect.downloadUrl, collect.duration, collect.coverSmall, collect.coverLarge, collect.playtimes, collect.downloadSize, collect.createdAt,collect.rePlayTime, collect.title];
    int result = sqlite3_exec(_dbPoint, [updateSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"更新"];
}
- (BOOL) updateCollected:(CollectedModel *)collect wheretitle:(NSString *)title
{
    NSString *updateSQL = [NSString stringWithFormat:@"update CollectedModel set address='%@',image='%@' where titelStr='%@'",collect.address, collect.image, title];
    int result = sqlite3_exec(_dbPoint, [updateSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"更新"];
}

- (BOOL) deleteVideo:(CollectModel *)collect
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from CollectModel where albumTitle='%@'", collect.albumTitle];
    int result = sqlite3_exec(_dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"删除"];
}

- (BOOL) deletecollect:(CollectedModel *)collect
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from CollectedModel where titelStr = '%@'", collect.titelStr];
    int result = sqlite3_exec(_dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    return [self udgeResult:result operation:@"删除"];
}
- (BOOL) deleteSearch:(NSArray *)searchArr
{
    int result = 0;
    for (SearchHistoryModel *searchModel in searchArr) {
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from SearchModel where titelStr = '%@'", searchModel.titelStr];
        result = sqlite3_exec(_dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    }
    return [self udgeResult:result operation:@"删除"];
}

// 查询所有
- (NSArray *)selectAllCollect
{
    NSString *selectAllSQL = [NSString stringWithFormat:@"select * from CollectModel"];
    
    sqlite3_stmt *stmt = nil;
    // 参数1:数据库指针
    // 参数2:sql语句
    // 参数3:限制sql语句的长度,-1就是不限制,防止了sql注入等攻击
    // 参数4:stmt作为一个临时数据库,保存sql语句执行的结果,然后再,同步到数据库文件中,这么做防止了在操作数据库时候,手机断电或者强制退出,而造成的脏数据
    
    int result = sqlite3_prepare_v2(_dbPoint, [selectAllSQL UTF8String], -1, &stmt, NULL);
    
    NSMutableArray *videoCollectArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 按照列提取数据,并把c语言的字符串转成NSString
            const unsigned int index = sqlite3_column_int(stmt, 0);
            NSInteger tempIndex = (NSInteger)index;
            
            const unsigned char *requstUrl = sqlite3_column_text(stmt, 1);
            NSString *temprequstUrl = [NSString stringWithUTF8String:requstUrl];
            
            const unsigned char *albumTitle = sqlite3_column_text(stmt, 2);
            NSString *tempalbumTitle = [NSString stringWithUTF8String:albumTitle];
            
            const unsigned char *AlbumCoverSmall = sqlite3_column_text(stmt, 3);
            NSString *tempAlbumCoverSmall = [NSString stringWithUTF8String:AlbumCoverSmall];
            const unsigned char *nickname = sqlite3_column_text(stmt, 4);
            NSString *tempNickName = [NSString stringWithUTF8String:nickname];
            
            const unsigned char *intro = sqlite3_column_text(stmt, 5);
            NSString *tempIntro = [NSString stringWithUTF8String:intro];
            
            const unsigned char *AlbumPlayTimes = sqlite3_column_text(stmt, 6);
            NSString *tempAlbumPlayTimes = [NSString stringWithUTF8String:AlbumPlayTimes];
            
            const unsigned char *playUrl64 = sqlite3_column_text(stmt, 7);
            NSString *tempplayUrl64 = [NSString stringWithUTF8String:playUrl64];
            
            const unsigned char *downloadUrl = sqlite3_column_text(stmt, 8);
            NSString *tempdownloadUrl = [NSString stringWithUTF8String:downloadUrl];
            
            const unsigned char *title = sqlite3_column_text(stmt, 9);
            NSString *temptitle = [NSString stringWithUTF8String:title];
            
            const unsigned char *duration = sqlite3_column_text(stmt, 10);
            NSString *tempduration = [NSString stringWithUTF8String:duration];
            
            const unsigned char *coverSmall = sqlite3_column_text(stmt, 11);
            NSString *tempcoverSmall = [NSString stringWithUTF8String:coverSmall];
            
            const unsigned char *coverLarge = sqlite3_column_text(stmt, 12);
            NSString *tempcoverLarge = [NSString stringWithUTF8String:coverLarge];
            
            const unsigned char *playtimes = sqlite3_column_text(stmt, 13);
            NSString *tempplaytimes = [NSString stringWithUTF8String:playtimes];
            
            const unsigned char *downloadSize = sqlite3_column_text(stmt, 14);
            NSString *tempdownloadSize = [NSString stringWithUTF8String:downloadSize];
            
            const unsigned char *createdAt = sqlite3_column_text(stmt, 15);
            NSString *tempcreatedAt = [NSString stringWithUTF8String:createdAt];
            const unsigned char *rePlayTime = sqlite3_column_text(stmt, 16);
            NSString *temprePlayTime = [NSString stringWithUTF8String:rePlayTime];
            
            CollectModel *FMDB = [CollectModel collectModelWithRequstUrl:temprequstUrl albumTitle:tempalbumTitle AlbumCoverSmall:tempAlbumCoverSmall index:tempIndex nickname:tempNickName intro:tempIntro AlbumPlayTimes:tempAlbumPlayTimes playUrl64:tempplayUrl64 downloadUrl:tempdownloadUrl title:temptitle duration:tempduration coverSmall:tempcoverSmall coverLarge:tempcoverLarge playtimes:tempplaytimes downloadSize:tempdownloadSize createdAt:tempcreatedAt rePlayTime:temprePlayTime];
            [videoCollectArr addObject:FMDB];
        }
    }
    
    // 把stmt保存的临时结果,同步到本地的数据库中,清除这个指针
    sqlite3_finalize(stmt);
    
    return videoCollectArr;
}

- (NSArray *)selectAllCollected
{
    NSString *selectAllSQL = [NSString stringWithFormat:@"select * from CollectedModel"];
    
    sqlite3_stmt *stmt = nil;
    // 参数1:数据库指针
    // 参数2:sql语句
    // 参数3:限制sql语句的长度,-1就是不限制,防止了sql注入等攻击
    // 参数4:stmt作为一个临时数据库,保存sql语句执行的结果,然后再,同步到数据库文件中,这么做防止了在操作数据库时候,手机断电或者强制退出,而造成的脏数据
    
    int result = sqlite3_prepare_v2(_dbPoint, [selectAllSQL UTF8String], -1, &stmt, NULL);
    
    NSMutableArray *videoCollectArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 按照列提取数据,并把c语言的字符串转成NSString
            
            const unsigned char *address = sqlite3_column_text(stmt, 0);
            NSString *tempaddress = [NSString stringWithUTF8String:address];
            
            const unsigned char *titleStr = sqlite3_column_text(stmt, 1);
            NSString *temptitleStr = [NSString stringWithUTF8String:titleStr];
            
            const unsigned char *image = sqlite3_column_text(stmt, 2);
            NSString *tempImage = [NSString stringWithUTF8String:image];
            
            
            
            CollectedModel *FMDB = [CollectedModel collectModelWithAddress:tempaddress TitelStr:temptitleStr Image:tempImage];
            [videoCollectArr addObject:FMDB];
        }
    }
    
    // 把stmt保存的临时结果,同步到本地的数据库中,清除这个指针
    sqlite3_finalize(stmt);
    
    return videoCollectArr;
}

- (NSArray *)selectAllSearch
{
    NSString *selectAllSQL = [NSString stringWithFormat:@"select * from SearchModel"];
    
    sqlite3_stmt *stmt = nil;
    // 参数1:数据库指针
    // 参数2:sql语句
    // 参数3:限制sql语句的长度,-1就是不限制,防止了sql注入等攻击
    // 参数4:stmt作为一个临时数据库,保存sql语句执行的结果,然后再,同步到数据库文件中,这么做防止了在操作数据库时候,手机断电或者强制退出,而造成的脏数据
    
    int result = sqlite3_prepare_v2(_dbPoint, [selectAllSQL UTF8String], -1, &stmt, NULL);
    
    NSMutableArray *videoCollectArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 按照列提取数据,并把c语言的字符串转成NSString
            
            const unsigned char *titleStr = sqlite3_column_text(stmt, 0);
            NSString *temptitleStr = [NSString stringWithUTF8String:titleStr];
            
            SearchHistoryModel *FMDB = [SearchHistoryModel collectModelWithStr:temptitleStr];
            [videoCollectArr addObject:FMDB];
        }
    }
    
    // 把stmt保存的临时结果,同步到本地的数据库中,清除这个指针
    sqlite3_finalize(stmt);
    
    return videoCollectArr;
}



//      // 条件所有
//- (CollectModel *)selectVideoByVideoStr:(NSString *)requstUrl
//{
//    NSString *selectOneSQL = [NSString stringWithFormat:@"select * from VideoCollect where address = '%@'", requstUrl];
//    
//    sqlite3_stmt *stmt = nil;
//    // 参数1:数据库指针
//    // 参数2:sql语句
//    // 参数3:限制sql语句的长度,-1就是不限制,防止了sql注入等攻击
//    // 参数4:stmt作为一个临时数据库,保存sql语句执行的结果,然后再,同步到数据库文件中,这么做防止了在操作数据库时候,手机断电或者强制退出,而造成的脏数据
//    
//    int result = sqlite3_prepare_v2(_dbPoint, [selectOneSQL UTF8String], -1, &stmt, NULL);
//    NSMutableArray *videoCollectArr = [NSMutableArray array];
//    CollectModel *videoDB = [[CollectModel alloc] init];
//    if (result == SQLITE_OK) {
//        while (sqlite3_step(stmt) == SQLITE_ROW) {
//           // 按照列提取数据,并把c语言的字符串转成NSString
//            
//            const unsigned int num = sqlite3_column_int(stmt, 0);
//            NSInteger tempNum = (NSInteger)num;
//            
//            const unsigned char *playTime = sqlite3_column_text(stmt, 1);
//            NSString *tempPlaytime = [NSString stringWithUTF8String:playTime];
//            
//            const unsigned char *album = sqlite3_column_text(stmt, 2);
//            NSString *tempAlbum = [NSString stringWithUTF8String:album];
//            
//            const unsigned char *image = sqlite3_column_text(stmt, 3);
//            NSString *tempImage = [NSString stringWithUTF8String:image];
//            
//            const unsigned char *titel = sqlite3_column_text(stmt, 4);
//            NSString *tempTitel = [NSString stringWithUTF8String:titel];
//            
//            const unsigned char *address = sqlite3_column_text(stmt, 5);
//            NSString *tempAddress = [NSString stringWithUTF8String:address];
//            
//            CollectModel *FMDB = [CollectModel collectModelWithAddress:tempAddress album:tempAlbum image:tempImage num:tempNum playTime:tempPlaytime titel:tempTitel];
//            [videoCollectArr addObject:FMDB];
//        }
//    }
//    // 把stmt保存的临时结果,同步到本地的数据库中,清除这个指针
//    sqlite3_finalize(stmt);
//    
//    return videoDB;
//}


@end
